# 人工智能NLP-Agent数字人项目-04-基金数据问答任务工单V1.1-2025.2.12
import csv
import sqlite3
import pandas as pd

import utils.configFinRAG as configFinRAG


def query_db(sql, cursor):
    """
    执行 SQL 查询并返回结果。
    :param sql: SQL 查询语句
    :param cursor: 数据库游标
    :return: 成功标志和执行结果
    """
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return 1, str(result)
    except Exception as e:
        print(f"Error executing SQL: {sql}. Error: {e}")
        return 0, "error"


if __name__ == '__main__':
    # 读取 SQL 文件
    question_sql_file = pd.read_csv(configFinRAG.question_sql_path, delimiter=",", header=0)

    # 打开 SQL 执行结果文件
    with open(configFinRAG.question_sql_check_path, 'w', newline='', encoding='utf-8-sig') as file:
        csvwriter = csv.writer(file)
        csvwriter.writerow(['问题id', '问题', 'SQL', 'flag', '执行结果'])

        # 数据库连接
        db_path = '/Users/wanghr/Documents/八维研修/项目/999-项目/fay数字人/金融场景智能问答系统/bs_challenge_financial_14b_dataset/dataset/博金杯比赛数据.db'
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 执行 SQL 并将结果写入文件
        for index, row in question_sql_file.iterrows():
            sql = row['SQL']
            if sql != 'error':
                success_flag, exc_result = query_db(sql, cursor)
                csvwriter.writerow([
                    row['问题id'],
                    row['问题'],
                    sql,
                    success_flag,
                    exc_result
                ])

        # 关闭数据库连接
        conn.close()
